package f;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

public class Db {

	private Connection conn;
	public Db(Connection conn) {
		this.conn = conn;
	}
	public static Db defaultDb() {
		return new Db(defaultConnection());
	}
	
	public Map<String, Object> select_f_hash(String f_hash) {
		Map<String, Object> map = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement("select * from jm_file where f_hash = ?");
			ps.setString(1, f_hash);
			rs = ps.executeQuery();
			if(rs.next()) {
				ResultSetMetaData rsmd = rs.getMetaData();
				int cc = rsmd.getColumnCount();
				map = new HashMap<String, Object>(cc);
				for(int i=1; i<=cc; i++) {
					map.put(rsmd.getColumnLabel(i).toUpperCase(), rs.getObject(i));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			close(conn, ps, rs);
		}
		return map;
	}
	
	public Map<String, Object> select_t_hash(String t_hash) {
		Map<String, Object> map = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement("select * from jm_file where t_hash = ?");
			ps.setString(1, t_hash);
			rs = ps.executeQuery();
			if(rs.next()) {
				ResultSetMetaData rsmd = rs.getMetaData();
				int cc = rsmd.getColumnCount();
				map = new HashMap<String, Object>(cc);
				for(int i=1; i<=cc; i++) {
					map.put(rsmd.getColumnLabel(i).toUpperCase(), rs.getObject(i));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			close(conn, ps, rs);
		}
		return map;
	}
	
	public Map<String, Object> select_f_path(String f_path) {
		Map<String, Object> map = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement("select * from jm_file where f_path = ?");
			ps.setString(1, f_path);
			rs = ps.executeQuery();
			if(rs.next()) {
				ResultSetMetaData rsmd = rs.getMetaData();
				int cc = rsmd.getColumnCount();
				map = new HashMap<String, Object>(cc);
				for(int i=1; i<=cc; i++) {
					map.put(rsmd.getColumnLabel(i).toUpperCase(), rs.getObject(i));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			close(conn, ps, rs);
		}
		return map;
	}
	
	public void init() {
		String sql = "create table if not exists jm_file(_id INTEGER PRIMARY KEY AUTOINCREMENT, f_type text, f_hash text unique, f_path text, f_ext text, f_key text, t_hash)";
		executeUpdate(conn, sql, null, true);
	}
	
	public int insert(String f_type, String f_hash, String f_path, String f_ext, String f_key, String t_hash) {
		String sql = "insert into jm_file(f_type, f_hash, f_path, f_ext, f_key, t_hash) values(:f_type, :f_hash, :f_path, :f_ext, :f_key, :t_hash)";
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("f_type", f_type);
		map.put("f_hash", f_hash);
		map.put("f_path", f_path);
		map.put("f_ext", f_ext);
		map.put("f_key", f_key);
		map.put("t_hash", t_hash);
		return executeUpdate(conn, sql, map, true);
	}
	
	public int delete(String t_hash) {
		String sql = "delete from jm_file where t_hash = :t_hash";
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("t_hash", t_hash);
		return executeUpdate(conn, sql, map, true);
	}
	
	public int deleteLike(String f_path) {
		//String sql = "delete from jm_file where f_path like :f_path";
		String sql = "delete from jm_file where f_path like :f_path and f_path not like :f_path2";
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("f_path", f_path+"%");
		map.put("f_path2", f_path+"%/%");
		return executeUpdate(conn, sql, map, true);
	}
	
	/* ************************************************************************************ */
	
	public static Connection defaultConnection() {
		return getSqliteConnection(Root.theDbFile());
	}
	
	public static int executeUpdate(Connection connection, String sql, Map<String, Object> map, boolean close) {
		NamedParameterStatement nps = null;
		try {
			nps = new NamedParameterStatement(connection, sql);
			if(map!=null && map.size()>0) {
				for(Map.Entry<String, Object> m: map.entrySet()) {
					nps.setObject(m.getKey(), m.getValue());
				}
			}
			return nps.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if(close) {
				close(connection, nps, null);
			}else {
				close(null, nps, null);
			}
		}
	}
	
	public static Connection getSqliteConnection(File db){
		String driverClass = "org.sqlite.JDBC";
		String jdbcUrl = "jdbc:sqlite:"+db.getAbsolutePath();
		return getConnection(driverClass, jdbcUrl, null, null);
	}
	
	public static Connection getConnection(String driverClass, String jdbcUrl, String user, String password){
		try {
			Class.forName(driverClass);
			return DriverManager.getConnection(jdbcUrl, user, password);
		} catch (ClassNotFoundException e) {
			throw new RuntimeException(e);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	public static void close(Connection connection, Statement statement, ResultSet resultSet){
		try {
			if(resultSet!=null) resultSet.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		try {
			if(statement!=null) statement.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		try {
			if(connection!=null) connection.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	public static void close(Connection connection, NamedParameterStatement statement, ResultSet resultSet){
		try {
			if(resultSet!=null) resultSet.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		try {
			if(statement!=null) statement.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		try {
			if(connection!=null) connection.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	
}
